package dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashSet;
import java.util.Set;

import models.AvaliacaoFisica;


public class DAOAvaliacaoFisica {

	public Set<AvaliacaoFisica> select() throws Exception {
		Set<AvaliacaoFisica> objs = new HashSet<AvaliacaoFisica>();
		try {

			String query = "select idAvaliacaoFisica, idUsuario, data, dataVencimento, apto, atestado from AvaliacaoFisica";

			Statement statement = DAOConnection.getConnection().createStatement();

			ResultSet resultSet = statement.executeQuery(query);

			while(resultSet.next()) {
				AvaliacaoFisica obj = new AvaliacaoFisica();
				obj.idAvaliacaoFisica = resultSet.getInt(1);
				obj.idUsuario = resultSet.getInt(2);
				obj.data = DAOConnection.date2StringPTBR(resultSet.getDate(3));
				obj.dataVencimento = DAOConnection.date2StringPTBR(resultSet.getDate(4));
				obj.apto = resultSet.getBoolean(5);
				//obj.atestado = resultSet.getImage(6);
				objs.add(obj);
			}
		} catch(SQLException e) {
			e.printStackTrace();
			throw new Exception();
		} finally {
			DAOConnection.closeConnection();
		}
		return objs; 
	}

	public AvaliacaoFisica select(int idAvaliacaoFisica) throws Exception {
		AvaliacaoFisica obj = null;
		try	{

			String query = "select idAvaliacaoFisica, idUsuario, data, dataVencimento, apto, atestado from AvaliacaoFisica where idAvaliacaoFisica = ?";

			PreparedStatement statement = DAOConnection.getConnection().prepareStatement(query);
			statement.setInt(1, idAvaliacaoFisica);
			ResultSet resultSet = statement.executeQuery();

			while(resultSet.next()) {
				obj = new AvaliacaoFisica();
				obj.idAvaliacaoFisica = resultSet.getInt(1);
				obj.idUsuario = resultSet.getInt(2);
				obj.data = DAOConnection.date2StringPTBR(resultSet.getDate(3));
				obj.dataVencimento = DAOConnection.date2StringPTBR(resultSet.getDate(4));
				obj.apto = resultSet.getBoolean(5);
				//obj.atestado = resultSet.getImage(6);

			}
		} catch(SQLException e) {
			e.printStackTrace();
			throw new Exception();
		} finally {
			DAOConnection.closeConnection();
		}
		return obj; 
	}

	public void insert(AvaliacaoFisica f) throws Exception {
		try {
			//String insert = "insert into AvaliacaoFisica(idAvaliacaoFisica, idUsuario, data, dataVencimento, apto, atestado) values(?, ?, ?, ?, ?, ?)";
			String insert = "insert into AvaliacaoFisica(idAvaliacaoFisica, idUsuario, data, dataVencimento, apto, atestado) values(?, ?, ?, ?, ?)";

			PreparedStatement statement = DAOConnection.getConnection().prepareStatement(insert);

			statement.setInt(1, f.idAvaliacaoFisica);
			statement.setInt(2, f.idUsuario);
			statement.setString(3, DAOConnection.date2StringSQL(DAOConnection.string2DateHTML(f.data)));
			statement.setString(4, DAOConnection.date2StringSQL(DAOConnection.string2DateHTML(f.dataVencimento)));
			statement.setBoolean(5, f.apto);
			//statement.setImage(6, f.atestado);

			statement.executeUpdate();
		} catch(SQLException e) {	
			throw new Exception();
		} finally {
			DAOConnection.closeConnection();
		}

	}

	public void update(AvaliacaoFisica f) throws Exception {
		try {

			String update = "update AvaliacaoFisic set idAvaliacaoFisica = ?, idUsuario = ? where idAvaliacaoFisica = ?";

			PreparedStatement statement = DAOConnection.getConnection().prepareStatement(update);

			statement.setInt(1, f.idUsuario);
			statement.setString(2, DAOConnection.date2StringSQL(DAOConnection.string2DateHTML(f.data)));
			statement.setString(3, DAOConnection.date2StringSQL(DAOConnection.string2DateHTML(f.dataVencimento)));
			statement.setBoolean(4, f.apto);
			statement.setInt(5, f.idAvaliacaoFisica);
			//statement.setImage(6, f.atestado);


			statement.executeUpdate();
		} catch(SQLException e) {
			throw new Exception();
		} finally {
			DAOConnection.closeConnection();
		}

	}

	public void delete(AvaliacaoFisica f) throws Exception {
		try {

			String delete = "delete from AvaliacaoFisica where idAvaliacaoFisica = ?";

			PreparedStatement statement = DAOConnection.getConnection().prepareStatement(delete);

			statement.setInt(1, f.idAvaliacaoFisica);

			statement.executeUpdate();
		} catch(SQLException e) {
			throw new Exception();
		} finally {
			DAOConnection.closeConnection();
		}
	}

}
